package data.dao.daoImplementation;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import data.dao.HolidaysDAO;
import data.dao.LeaveDAOException;
import data.dto.HolidaysDTO;
import data.searchdto.HolidaySearchDTO;

public class HolidayDAOImpl implements HolidaysDAO  {

	@Override
	public ArrayList<HolidaysDTO> findHoiliday(HolidaySearchDTO holidaySearchdto)
			throws LeaveDAOException, SQLException {
		ArrayList<HolidaysDTO> list = null;
        try {
             
        	list = singleQuery(holidaySearchdto);

        } finally {
           
        }
        return list;
	}

	@Override
	public ArrayList<HolidaysDTO> findAllHoliday() throws LeaveDAOException,
			SQLException {

		String sql = "SELECT * FROM holidays WHERE isactive = 1 ORDER BY id ASC ";
		ArrayList<HolidaysDTO> searchResults = listQuery(util.Database.getInstance().getConnection().prepareStatement(sql));

        return searchResults;
	}

	@Override
	public void insertHoliday(HolidaysDTO valueObject)
			throws LeaveDAOException, SQLException {
		 String sql = "";
         PreparedStatement stmt = null;

         try {
              sql = "INSERT INTO holidays ( HolidayName, HolidayDate, "
              + "status,isactive) VALUES (?, ?, ?,1 ) ";
              stmt = util.Database.getInstance().getConnection().prepareStatement(sql);

              //stmt.setInt(1, valueObject.getId()); 
              stmt.setString(1, valueObject.getHolidayName()); 
              stmt.setTimestamp(2, valueObject.getHolidayDate()); 
              //stmt.setTimestamp(3, util.CurrentDate.getCurrentTimeStamp()); 
              //stmt.setInt(4, valueObject.getCreatedBy()); 
              //stmt.setTimestamp(5, util.CurrentDate.getCurrentTimeStamp()); 
              //stmt.setInt(6, valueObject.getModifiedBy()); 
              stmt.setInt(3, valueObject.getStatus()); 

              int rowcount = util.Database.getInstance().executeUpdate(stmt);
              if (rowcount != 1) {
                   //System.out.println("PrimaryKey Error when updating DB!");
                   throw new SQLException("PrimaryKey Error when updating DB!");
              }

         } finally {
             if (stmt != null)
                 stmt.close();
         }
		
	}

	@Override
	public void updateHoliday(HolidaysDTO valueObject)
			throws LeaveDAOException, SQLException, NotFoundException {
		String sql = "UPDATE holidays SET HolidayName = ?, HolidayDate = ?, "
            + "status = ? WHERE (id = ? ) ";
       PreparedStatement stmt = null;

       try {
           stmt = util.Database.getInstance().getConnection().prepareStatement(sql);
           stmt.setString(1, valueObject.getHolidayName()); 
           stmt.setTimestamp(2, valueObject.getHolidayDate()); 
           //stmt.setTimestamp(3, util.CurrentDate.getCurrentTimeStamp()); 
           //stmt.setInt(4, util.CurrentUser.getInstance().getUserId()); 
           stmt.setInt(3, valueObject.getStatus()); 

           stmt.setInt(4, valueObject.getId()); 

           int rowcount = util.Database.getInstance().executeUpdate(stmt);
           if (rowcount == 0) {
                //System.out.println("Object could not be saved! (PrimaryKey not found)");
                throw new NotFoundException("Object could not be saved! (PrimaryKey not found)");
           }
           if (rowcount > 1) {
                //System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
                throw new SQLException("PrimaryKey Error when updating DB! (Many objects were affected!)");
           }
       } finally {
           if (stmt != null)
               stmt.close();
       }
		
	}

	@Override
	public void deleteHoliday(int LeaveDetail)
			throws LeaveDAOException, SQLException, NotFoundException {
		String sql = "UPDATE holidays SET isactive = 0 WHERE (id = ? ) ";
        PreparedStatement stmt = null;

        try {
            stmt = util.Database.getInstance().getConnection().prepareStatement(sql);
            stmt.setInt(1, LeaveDetail); 

            int rowcount = util.Database.getInstance().executeUpdate(stmt);
            if (rowcount == 0) {
                 //System.out.println("Object could not be deleted (PrimaryKey not found)");
                 throw new NotFoundException("Object could not be deleted! (PrimaryKey not found)");
            }
            if (rowcount > 1) {
                 //System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
                 throw new SQLException("PrimaryKey Error when updating DB! (Many objects were deleted!)");
            }
        } finally {
            if (stmt != null)
                stmt.close();
        }
		
	}
	
	protected ArrayList<HolidaysDTO> listQuery(PreparedStatement stmt) throws SQLException {

		ArrayList<HolidaysDTO> searchResults = new ArrayList<HolidaysDTO>();
        ResultSet result = null;

        try {
            result = util.Database.getInstance().executeQuery(stmt);

            while (result.next()) {
            	HolidaysDTO temp = new HolidaysDTO();

                 temp.setId(result.getInt("id")); 
                 temp.setHolidayName(result.getString("HolidayName")); 
                 temp.setHolidayDate(result.getTimestamp("HolidayDate")); 
                 //temp.setCreatedOn(result.getTimestamp("createdOn")); 
                 //temp.setCreatedBy(result.getInt("createdBy")); 
                 //temp.setModifiedOn(result.getTimestamp("modifiedOn")); 
                 //temp.setModifiedBy(result.getInt("modifiedBy")); 
                 temp.setStatus(result.getShort("status")); 

                 searchResults.add(temp);
            }

        } finally {
            if (result != null)
                result.close();
            if (stmt != null)
                stmt.close();
        }

        return searchResults;
  }
	
	public ArrayList<HolidaysDTO> singleQuery(HolidaySearchDTO valueObject) throws SQLException {

		ArrayList<HolidaysDTO> searchResults;

        boolean first = true;
        StringBuffer sql = new StringBuffer("SELECT * FROM holidays WHERE isactive = 1 AND 1=1 ");

        if (valueObject.getHolidayName() != null) {
            if (first) { first = false; }
            sql.append("AND HolidayName LIKE '").append(valueObject.getHolidayName()).append("%' ");
        }

        if (valueObject.getStartDate() != null) {
            if (first) { first = false; }
            sql.append("AND HolidayDate >= '").append(valueObject.getStartDate()).append("' ");
        }

        if (valueObject.getEndDate() != null) {
            if (first) { first = false; }
            sql.append("AND HolidayDate <= '").append(valueObject.getEndDate()).append("' ");
        }

        sql.append("ORDER BY id ASC ");

        // Prevent accidential full table results.
        // Use loadAll if all rows must be returned.
        if (first)
             searchResults = new ArrayList<HolidaysDTO>();
        else
             searchResults = listQuery(util.Database.getInstance().getConnection().prepareStatement(sql.toString()));

        return searchResults;
  }

}
